"""
Case Type   : interval
Case Name   : python驱动pyog,测试interval表达式测试列引用
Create At   : 2024/9/23
Owner       : @zhanghuan96
Description :
    1.创建兼容B库，检查dolphin插件
    2.配置pg_hba入口
    3.连接数据库
    4.建表，插入并查询数据
    5.删除表
    6.断开连接
    7.清理环境
Expect      :
    1.创建成功，插件自动加载
    2.执行成功
    3.连接成功，db.state返回'idle'
    4.成功
    5.删除成功
    6.执行成功，db.state返回'closed'
    7.成功
History     :
"""

import datetime
import os
import unittest

import py_opengauss
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class PyogSupportInterval(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.pri_user = Node('PrimaryDbUser')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = 'db_interval_0020'
        self.table_name = 't_interval_0020'
        cmd = f"cp {os.path.join(macro.DB_INSTANCE_PATH, 'pg_hba.conf')}  " \
              f"{os.path.join(macro.DB_INSTANCE_PATH, 'pg_hba.conf_t_bak')}"
        self.log.info(cmd)
        backup_res = self.pri_user.sh(cmd).result()
        self.log.info(backup_res)

    def test_binary(self):
        text = '-----step1:创建兼容B库，检查dolphin插件;expect:创建成功-----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"create database {self.db_name} " \
                  f"dbcompatibility ='B';"
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd, dbname='postgres')
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, sql_res,
                      "执行失败" + text)

        sql_cmd = "select extname from pg_extension where extname ='dolphin';"
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd,
                                            dbname=self.db_name)
        self.log.info(sql_res)
        self.assertIn('dolphin', sql_res, '查询失败')

        text = '-----step2: 配置pg_hba入口 expect: 成功-----'
        self.log.info(text)
        host_cmd = "ifconfig -a|grep inet6 -a2|" \
                   "grep broadcast|awk '{print $2}'"
        self.log.info(host_cmd)
        self.host = os.popen(host_cmd).readlines()[0].strip()
        guc_cmd = f'source {macro.DB_ENV_PATH}; ' \
                  f'gs_guc reload -D {macro.DB_INSTANCE_PATH} ' \
                  f'-h "host {self.db_name} {self.pri_user.db_user} ' \
                  f'{self.host}/32 sha256"'
        self.log.info(guc_cmd)
        guc_res = self.pri_user.sh(guc_cmd).result()
        self.log.info(guc_res)
        self.assertIn(self.constant.GSGUC_SUCCESS_MSG, guc_res, text)

        text = '----step3: 连接数据库 expect: 成功----'
        self.log.info(text)
        conn_info = f'opengauss://{self.pri_user.db_user}:' \
                    f'{self.pri_user.db_password}@{self.pri_user.db_host}:' \
                    f'{self.pri_user.db_port}/{self.db_name}'
        self.log.info(conn_info)
        db = py_opengauss.open(conn_info)
        self.log.info(db.state)
        self.assertEqual('idle', db.state, text)

        text = '----step4: 建表，插入并查询数据 expect: 成功----'
        self.log.info(text)
        cmd = f'''drop table if exists {self.table_name};
        create table {self.table_name}(v1 int);
        insert into {self.table_name} values(1);       
        '''
        self.log.info(cmd)
        res = db.execute(cmd)
        self.assertIsNone(res)
        self.log.info(res)

        cmd = f"select '2024-06-18 18:30:00' + interval 1 + v1 year from {self.table_name};"
        self.log.info(cmd)
        sql = db.prepare(cmd)
        self.log.info(sql())
        expect = [(datetime.datetime(2026, 6, 18, 18, 30),)]
        self.assertEqual(sql(), expect, text)

        text = '----step5: 删除表 expect: 成功----'
        self.log.info(text)
        cmd = f'drop table if exists {self.table_name};'
        res = db.execute(cmd)
        self.log.info(res)
        self.assertIsNone(res)

        text = '----step6: 断开连接 expect: 成功----'
        self.log.info(text)
        db.close()
        self.assertEqual('closed', db.state, text)

    def tearDown(self):
        self.log.info('----step7:清理环境;expect:成功----')
        sql_res = self.pri_sh.execut_db_sql(
            f"drop database if exists {self.db_name};", dbname='postgres')
        self.log.info(sql_res)
        cmd = f"rm -rf " \
              f"{os.path.join(macro.DB_INSTANCE_PATH, 'pg_hba.conf')};" \
              f"mv " \
              f"{os.path.join(macro.DB_INSTANCE_PATH, 'pg_hba.conf_t_bak')} " \
              f"{os.path.join(macro.DB_INSTANCE_PATH, 'pg_hba.conf')}"
        self.log.info(cmd)
        restore_res = self.pri_user.sh(cmd).result()
        self.log.info(self.log.info(restore_res))
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, sql_res,
                      "执行失败")
        self.log.info(f"-----{os.path.basename(__file__)} end-----")
